
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 09/22/2010 00:15:04
-- Generated from EDMX file: C:\Documents and Settings\chacheon\My Documents\Visual Studio 2010\Projects\MVCStore\MVCStore\Models\eStore.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [eStore];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CategoryProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Products] DROP CONSTRAINT [FK_CategoryProduct];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductProductImage]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductImages] DROP CONSTRAINT [FK_ProductProductImage];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductProductReview]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductReviews] DROP CONSTRAINT [FK_ProductProductReview];
GO
IF OBJECT_ID(N'[dbo].[FK_OrderOrderDetail]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OrderDetails] DROP CONSTRAINT [FK_OrderOrderDetail];
GO
IF OBJECT_ID(N'[dbo].[FK_UserProductReview]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProductReviews] DROP CONSTRAINT [FK_UserProductReview];
GO
IF OBJECT_ID(N'[dbo].[FK_UserOrder]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [FK_UserOrder];
GO
IF OBJECT_ID(N'[dbo].[FK_UserCart_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserCart] DROP CONSTRAINT [FK_UserCart_User];
GO
IF OBJECT_ID(N'[dbo].[FK_UserCart_Cart]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserCart] DROP CONSTRAINT [FK_UserCart_Cart];
GO
IF OBJECT_ID(N'[dbo].[FK_WishlistWishlistItem]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WishlistItems] DROP CONSTRAINT [FK_WishlistWishlistItem];
GO
IF OBJECT_ID(N'[dbo].[FK_UserWishlist]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Wishlists] DROP CONSTRAINT [FK_UserWishlist];
GO
IF OBJECT_ID(N'[dbo].[FK_WishlistItemProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WishlistItems] DROP CONSTRAINT [FK_WishlistItemProduct];
GO
IF OBJECT_ID(N'[dbo].[FK_OrderDetailProduct]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OrderDetails] DROP CONSTRAINT [FK_OrderDetailProduct];
GO
IF OBJECT_ID(N'[dbo].[FK_CartCartItem]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CartItems] DROP CONSTRAINT [FK_CartCartItem];
GO
IF OBJECT_ID(N'[dbo].[FK_ProductCartItem]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CartItems] DROP CONSTRAINT [FK_ProductCartItem];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Products]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Products];
GO
IF OBJECT_ID(N'[dbo].[Categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Categories];
GO
IF OBJECT_ID(N'[dbo].[ProductImages]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductImages];
GO
IF OBJECT_ID(N'[dbo].[Carts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Carts];
GO
IF OBJECT_ID(N'[dbo].[Orders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Orders];
GO
IF OBJECT_ID(N'[dbo].[OrderDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OrderDetails];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[ProductReviews]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProductReviews];
GO
IF OBJECT_ID(N'[dbo].[Wishlists]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Wishlists];
GO
IF OBJECT_ID(N'[dbo].[WishlistItems]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WishlistItems];
GO
IF OBJECT_ID(N'[dbo].[CartItems]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CartItems];
GO
IF OBJECT_ID(N'[dbo].[UserCart]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserCart];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Products'
CREATE TABLE [dbo].[Products] (
    [ProductId] int IDENTITY(1,1) NOT NULL,
    [ProductName] nvarchar(max)  NOT NULL,
    [ProductQty] int  NOT NULL,
    [ProductDesc] nvarchar(max)  NULL,
    [ProductImageUrl] nvarchar(max)  NULL,
    [CategoryCategoryId] int  NOT NULL,
    [ProductWeight] float  NOT NULL,
    [ProductUnitPrice] decimal(18,0)  NOT NULL,
    [CreatedDate] datetime  NOT NULL
);
GO

-- Creating table 'Categories'
CREATE TABLE [dbo].[Categories] (
    [CategoryId] int IDENTITY(1,1) NOT NULL,
    [CategoryName] nvarchar(max)  NOT NULL,
    [CategoryDesc] nvarchar(max)  NULL
);
GO

-- Creating table 'ProductImages'
CREATE TABLE [dbo].[ProductImages] (
    [ProductImageId] int IDENTITY(1,1) NOT NULL,
    [ProductImageUrl] nvarchar(max)  NOT NULL,
    [ProductProductId] int  NOT NULL
);
GO

-- Creating table 'Carts'
CREATE TABLE [dbo].[Carts] (
    [RecordId] int IDENTITY(1,1) NOT NULL,
    [SessionId] nvarchar(max)  NULL,
    [CartQty] int  NOT NULL,
    [CreatedDate] datetime  NULL
);
GO

-- Creating table 'Orders'
CREATE TABLE [dbo].[Orders] (
    [OrderId] int IDENTITY(1,1) NOT NULL,
    [OrderDate] datetime  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [UserUserId] int  NOT NULL,
    [TotalOrderPrice] decimal(18,0)  NOT NULL,
    [ShippingAddress1] nvarchar(max)  NOT NULL,
    [ShippingAddress2] nvarchar(max)  NULL,
    [Postcode] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'OrderDetails'
CREATE TABLE [dbo].[OrderDetails] (
    [OrderDetailId] int IDENTITY(1,1) NOT NULL,
    [Quantity] int  NOT NULL,
    [UnitPrice] decimal(10,2)  NOT NULL,
    [OrderOrderId] int  NOT NULL,
    [ProductProductId] int  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserId] int IDENTITY(1,1) NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [DateCreated] datetime  NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [IsAdmin] bit  NULL
);
GO

-- Creating table 'ProductReviews'
CREATE TABLE [dbo].[ProductReviews] (
    [ReviewId] int IDENTITY(1,1) NOT NULL,
    [ReviewComment] nvarchar(max)  NOT NULL,
    [StarRate] int  NOT NULL,
    [ProductProductId] int  NOT NULL,
    [CreatedDate] datetime  NOT NULL,
    [UserUserId] int  NOT NULL
);
GO

-- Creating table 'Wishlists'
CREATE TABLE [dbo].[Wishlists] (
    [WishlistId] int IDENTITY(1,1) NOT NULL,
    [DateCreated] nvarchar(max)  NOT NULL,
    [UserUserId] int  NOT NULL
);
GO

-- Creating table 'WishlistItems'
CREATE TABLE [dbo].[WishlistItems] (
    [WishItemId] int IDENTITY(1,1) NOT NULL,
    [WishlistWishlistId] int  NOT NULL,
    [Product_ProductId] int  NOT NULL
);
GO

-- Creating table 'CartItems'
CREATE TABLE [dbo].[CartItems] (
    [CartItemId] int IDENTITY(1,1) NOT NULL,
    [CartRecordId] int  NOT NULL,
    [CartItemQty] int  NOT NULL,
    [ProductProductId] int  NOT NULL
);
GO

-- Creating table 'UserCart'
CREATE TABLE [dbo].[UserCart] (
    [Users_UserId] int  NOT NULL,
    [Carts_RecordId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ProductId] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [PK_Products]
    PRIMARY KEY CLUSTERED ([ProductId] ASC);
GO

-- Creating primary key on [CategoryId] in table 'Categories'
ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [PK_Categories]
    PRIMARY KEY CLUSTERED ([CategoryId] ASC);
GO

-- Creating primary key on [ProductImageId] in table 'ProductImages'
ALTER TABLE [dbo].[ProductImages]
ADD CONSTRAINT [PK_ProductImages]
    PRIMARY KEY CLUSTERED ([ProductImageId] ASC);
GO

-- Creating primary key on [RecordId] in table 'Carts'
ALTER TABLE [dbo].[Carts]
ADD CONSTRAINT [PK_Carts]
    PRIMARY KEY CLUSTERED ([RecordId] ASC);
GO

-- Creating primary key on [OrderId] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [PK_Orders]
    PRIMARY KEY CLUSTERED ([OrderId] ASC);
GO

-- Creating primary key on [OrderDetailId] in table 'OrderDetails'
ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [PK_OrderDetails]
    PRIMARY KEY CLUSTERED ([OrderDetailId] ASC);
GO

-- Creating primary key on [UserId] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [ReviewId] in table 'ProductReviews'
ALTER TABLE [dbo].[ProductReviews]
ADD CONSTRAINT [PK_ProductReviews]
    PRIMARY KEY CLUSTERED ([ReviewId] ASC);
GO

-- Creating primary key on [WishlistId] in table 'Wishlists'
ALTER TABLE [dbo].[Wishlists]
ADD CONSTRAINT [PK_Wishlists]
    PRIMARY KEY CLUSTERED ([WishlistId] ASC);
GO

-- Creating primary key on [WishItemId] in table 'WishlistItems'
ALTER TABLE [dbo].[WishlistItems]
ADD CONSTRAINT [PK_WishlistItems]
    PRIMARY KEY CLUSTERED ([WishItemId] ASC);
GO

-- Creating primary key on [CartItemId] in table 'CartItems'
ALTER TABLE [dbo].[CartItems]
ADD CONSTRAINT [PK_CartItems]
    PRIMARY KEY CLUSTERED ([CartItemId] ASC);
GO

-- Creating primary key on [Users_UserId], [Carts_RecordId] in table 'UserCart'
ALTER TABLE [dbo].[UserCart]
ADD CONSTRAINT [PK_UserCart]
    PRIMARY KEY NONCLUSTERED ([Users_UserId], [Carts_RecordId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CategoryCategoryId] in table 'Products'
ALTER TABLE [dbo].[Products]
ADD CONSTRAINT [FK_CategoryProduct]
    FOREIGN KEY ([CategoryCategoryId])
    REFERENCES [dbo].[Categories]
        ([CategoryId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryProduct'
CREATE INDEX [IX_FK_CategoryProduct]
ON [dbo].[Products]
    ([CategoryCategoryId]);
GO

-- Creating foreign key on [ProductProductId] in table 'ProductImages'
ALTER TABLE [dbo].[ProductImages]
ADD CONSTRAINT [FK_ProductProductImage]
    FOREIGN KEY ([ProductProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductProductImage'
CREATE INDEX [IX_FK_ProductProductImage]
ON [dbo].[ProductImages]
    ([ProductProductId]);
GO

-- Creating foreign key on [ProductProductId] in table 'ProductReviews'
ALTER TABLE [dbo].[ProductReviews]
ADD CONSTRAINT [FK_ProductProductReview]
    FOREIGN KEY ([ProductProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductProductReview'
CREATE INDEX [IX_FK_ProductProductReview]
ON [dbo].[ProductReviews]
    ([ProductProductId]);
GO

-- Creating foreign key on [OrderOrderId] in table 'OrderDetails'
ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [FK_OrderOrderDetail]
    FOREIGN KEY ([OrderOrderId])
    REFERENCES [dbo].[Orders]
        ([OrderId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OrderOrderDetail'
CREATE INDEX [IX_FK_OrderOrderDetail]
ON [dbo].[OrderDetails]
    ([OrderOrderId]);
GO

-- Creating foreign key on [UserUserId] in table 'ProductReviews'
ALTER TABLE [dbo].[ProductReviews]
ADD CONSTRAINT [FK_UserProductReview]
    FOREIGN KEY ([UserUserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserProductReview'
CREATE INDEX [IX_FK_UserProductReview]
ON [dbo].[ProductReviews]
    ([UserUserId]);
GO

-- Creating foreign key on [UserUserId] in table 'Orders'
ALTER TABLE [dbo].[Orders]
ADD CONSTRAINT [FK_UserOrder]
    FOREIGN KEY ([UserUserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserOrder'
CREATE INDEX [IX_FK_UserOrder]
ON [dbo].[Orders]
    ([UserUserId]);
GO

-- Creating foreign key on [Users_UserId] in table 'UserCart'
ALTER TABLE [dbo].[UserCart]
ADD CONSTRAINT [FK_UserCart_User]
    FOREIGN KEY ([Users_UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Carts_RecordId] in table 'UserCart'
ALTER TABLE [dbo].[UserCart]
ADD CONSTRAINT [FK_UserCart_Cart]
    FOREIGN KEY ([Carts_RecordId])
    REFERENCES [dbo].[Carts]
        ([RecordId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserCart_Cart'
CREATE INDEX [IX_FK_UserCart_Cart]
ON [dbo].[UserCart]
    ([Carts_RecordId]);
GO

-- Creating foreign key on [WishlistWishlistId] in table 'WishlistItems'
ALTER TABLE [dbo].[WishlistItems]
ADD CONSTRAINT [FK_WishlistWishlistItem]
    FOREIGN KEY ([WishlistWishlistId])
    REFERENCES [dbo].[Wishlists]
        ([WishlistId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WishlistWishlistItem'
CREATE INDEX [IX_FK_WishlistWishlistItem]
ON [dbo].[WishlistItems]
    ([WishlistWishlistId]);
GO

-- Creating foreign key on [UserUserId] in table 'Wishlists'
ALTER TABLE [dbo].[Wishlists]
ADD CONSTRAINT [FK_UserWishlist]
    FOREIGN KEY ([UserUserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserWishlist'
CREATE INDEX [IX_FK_UserWishlist]
ON [dbo].[Wishlists]
    ([UserUserId]);
GO

-- Creating foreign key on [Product_ProductId] in table 'WishlistItems'
ALTER TABLE [dbo].[WishlistItems]
ADD CONSTRAINT [FK_WishlistItemProduct]
    FOREIGN KEY ([Product_ProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WishlistItemProduct'
CREATE INDEX [IX_FK_WishlistItemProduct]
ON [dbo].[WishlistItems]
    ([Product_ProductId]);
GO

-- Creating foreign key on [CartRecordId] in table 'CartItems'
ALTER TABLE [dbo].[CartItems]
ADD CONSTRAINT [FK_CartCartItem]
    FOREIGN KEY ([CartRecordId])
    REFERENCES [dbo].[Carts]
        ([RecordId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CartCartItem'
CREATE INDEX [IX_FK_CartCartItem]
ON [dbo].[CartItems]
    ([CartRecordId]);
GO

-- Creating foreign key on [ProductProductId] in table 'CartItems'
ALTER TABLE [dbo].[CartItems]
ADD CONSTRAINT [FK_ProductCartItem]
    FOREIGN KEY ([ProductProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductCartItem'
CREATE INDEX [IX_FK_ProductCartItem]
ON [dbo].[CartItems]
    ([ProductProductId]);
GO

-- Creating foreign key on [ProductProductId] in table 'OrderDetails'
ALTER TABLE [dbo].[OrderDetails]
ADD CONSTRAINT [FK_ProductOrderDetail]
    FOREIGN KEY ([ProductProductId])
    REFERENCES [dbo].[Products]
        ([ProductId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProductOrderDetail'
CREATE INDEX [IX_FK_ProductOrderDetail]
ON [dbo].[OrderDetails]
    ([ProductProductId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------